import 'package:sqflite/sqflite.dart';
import 'package:path/path.dart' as path;

import 'DBScheme.dart';

class DBHelper {
  static String? databasesPath;

  Future<String> get realDbPath async {
    if(databasesPath != null) return path.join(databasesPath!, dbPath);
    databasesPath = await getDatabasesPath();
    return path.join(databasesPath!, dbPath);
  }

  Future<List<String>> get existColumns async {
    List<Map<String, dynamic>> data = await db!.rawQuery("PRAGMA table_info('$tableName')");
    return data.map<String>((item)=> item["name"] as String).toList();
  }

  Database? db;
  final String tableName;
  final List<DBScheme> schemes;
  final List<String> columnNames;
  final String createTableSQL;
  final String dbPath;
  final int version;

  DBHelper({
    this.dbPath = "database.db",
    required this.tableName,
    required this.schemes,
    this.version = 1
  }) :
    columnNames = schemes.map((item)=> item.columnName).toList(),
    createTableSQL = '''
    create table $tableName (${schemes.map((item)=> item.column).join(",")})
    '''
  ;

  Future open() async {
    String dbPath = await realDbPath;
    if(db == null || !db!.isOpen) {
      db = await openDatabase(dbPath, version: version, 
        onCreate: (Database db, int version) async {
          var batch = db.batch();
          batch.execute(createTableSQL);
          await batch.commit();
        },
        onUpgrade: (Database db, int oldVersion, int newVersion) async {
          List<Map<String, dynamic>> data = await db.rawQuery("PRAGMA table_info('$tableName')");
          List<String> existColumns = data.map<String>((item)=> item["name"] as String).toList();
          if(oldVersion < newVersion) {
            var batch = db.batch();
            List<DBScheme> columns = schemes.where((item)=> item.sinceFrom > oldVersion && !existColumns.contains(item.columnName)).toList();
            for (var column in columns) {
              String updateTableSQL = column.addColumn(tableName);
              batch.execute(updateTableSQL);
            }
            await batch.commit();
          }
        }
      );
    }
  }

  Map<String,dynamic> decode(Map<String,dynamic> record) {
    Map<String,dynamic> map = {};
    for (var item in schemes) {
      map.addAll(item.decode(record));
    }
    return map;
  }

  Map<String,dynamic> convert(Map<String,dynamic> record) {
    Map<String,dynamic> map = {};
    for (var item in schemes) {
      map.addAll(
        item.convert(record)
      );
    }
    return map;
  }

  Future<int> insert(Map<String,dynamic> record) async {
    Map<String,dynamic> map = convert(record);
    if(map["_id"] == null) map.remove("_id");
    int id = await db!.insert(tableName, map);
    return id;
  }

  Future<List<Map<String,dynamic>>?> where(String where, List whereArgs, {
    int? limit,
    int? offset,
    String? orderBy,
  }) async {
    List<Map<String, dynamic>> maps = await db!.query(
      tableName,
      distinct: true,
      columns: columnNames,
      where: where,
      whereArgs: whereArgs,
      limit: limit,
      offset: offset,
      orderBy: orderBy,
    );
    if (maps.isNotEmpty) {
      return maps.map((map)=> decode(map)).toList();
    }
    return null;
  }

  Future<Map<String,dynamic>?> findOneById(int id) async {
    List<Map<String,dynamic>> maps = await db!.query(
      tableName,
      columns: columnNames,
      where: '_id = ?',
      whereArgs: [id]
    );
    if (maps.isNotEmpty) {
      return decode(maps.first);
    }
    return null;
  }

  Future<int> delete(String? id,[String? where, List? whereArgs]) async {
    return await db!.delete(tableName, where: where ?? '_id = ?', whereArgs: whereArgs ?? [id]);
  }

  Future<int> update(Map<String,dynamic> _json,[String? where, List? whereArgs]) async {
    Map<String,dynamic> map = convert(_json);
    return await db!.update(tableName, map, where: where ?? '_id = ?', whereArgs: whereArgs ?? [map["_id"]]);
  }

  Future close() async => db!.close();
}